Skip Headers

Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Making User-Managed Backups of the Control File

B ack up the control file of a database after making a structural modification to a database operating in `ARCHIVELOG` mode. To back up a database's control file, you must have the `ALTER` `DATABASE` system privilege.

## Backing Up the Control File to a Binary File

The primary method for backing up the control file is to use a SQL statement to generate a binary file. A binary backup is preferable to a trace file backup because it contains additional information such as the archived log history, offline range for read-only and offline tablespaces, and backup sets and copies (if you use RMAN). Note that binary control file backups do not include tempfile entries.

To back up the control file after a structural change:

1. Make the desired change to the database. For example, you may create a new tablespace:

       CREATE TABLESPACE tbs_1 DATAFILE 'file_1.f' SIZE 10M;

2. Back up the database's control file, specifying a filename for the output binary file. The following example backs up a control file to `/disk1/backup/cf.bak`:

       ALTER DATABASE BACKUP CONTROLFILE TO '/disk1/backup/cf.bak' REUSE;

   Specify the `REUSE` option to make the new control file overwrite one that currently exists.

## Backing Up the Control File to a Trace File

To back up the control file to a trace file, mount or open the database and issue the following SQL statement:

    ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

If you specify neither the `RESETLOGS` nor `NORESETLOGS` option in the SQL statement, then the resulting trace file contains versions of the control file for both `RESETLOGS` and `NORESETLOGS` options. Tempfile entries are included in the output using "`ALTER TABLESPACE... ADD TEMPFILE`" statements.

See Also: "Recovery of Read-Only Files with a Re-Created Control File" for special issues relating to read-only, offline normal, and temporary files included in `CREATE` `CONTROLFILE` statements

### Backing Up the Control File to a Trace File: Example

Assume that you want to generate a script that re-creates the control file for the `sales` database. The database has these characteristics:

- Three threads are enabled, of which thread 2 is public and thread 3 is private.

- The redo logs are multiplexed into three groups of two members each.

- The database has the following datafiles:

  - `/diska/prod/sales/db/filea.dbf` (offline datafile in online tablespace)

  - `/diska/prod/sales/db/database1.dbf` (online in `SYSTEM` tablespace)

  - `/diska/prod/sales/db/fileb.dbf` (only file in read-only tablespace)

You issue the following statement to create a trace file containing a `CREATE` `CONTROLFILE` `...` `NORESETLOGS` statement:

    ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;

You then edit the trace file to create a script that creates a new control file for the `sales` database based on the control file that was current when you generated the trace file. To avoid recovering offline normal or read-only tablespaces, edit them out of the `CREATE` `CONTROLFILE` statement in the trace file. When you open the database with the re-created control file, the dictionary check code will mark these omitted files as `MISSING`. You can run an `ALTER` `DATABASE` `RENAME` `FILE` statement renames them back to their original filenames.

For example, you can edit the `CREATE` `CONTROLFILE` `...` `NORESETLOGS` script in the trace file as follows, renaming files labeled `MISSING`:

    # The following statements will create a new control file and use it to open the
    # database. Log history and RMAN metadata will be lost. Additional logs may be
    # required for media recovery of offline datafiles. Use this only if the current
    # version of all online logs are available.

    STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG
         MAXLOGFILES 32
         MAXLOGMEMBERS 2
         MAXDATAFILES 32
         MAXINSTANCES 16
         MAXLOGHISTORY 1600
    LOGFILE
         GROUP 1
           '/diska/prod/sales/db/log1t1.dbf',
           '/diskb/prod/sales/db/log1t2.dbf'
         )  SIZE 100K
        GROUP 2
           '/diska/prod/sales/db/log2t1.dbf',
            '/diskb/prod/sales/db/log2t2.dbf'
        ) SIZE 100K,
        GROUP 3
           '/diska/prod/sales/db/log3t1.dbf',
           '/diskb/prod/sales/db/log3t2.dbf'
        ) SIZE 100K
    DATAFILE
        '/diska/prod/sales/db/database1.dbf',
        '/diskb/prod/sales/db/filea.dbf'
    ;

    # This datafile is offline, but its tablespace is online. Take the datafile
    # offline manually.
    ALTER DATABASE DATAFILE '/diska/prod/sales/db/filea.dbf' OFFLINE;

    # Recovery is required if any datafiles are restored backups,
    # or if the most recent shutdown was not normal or immediate.
    RECOVER DATABASE;

    # All redo logs need archiving and a log switch is needed.
    ALTER SYSTEM ARCHIVE LOG ALL;

    # The database can now be opened normally.
    ALTER DATABASE OPEN;

    # The backup control file does not list read-only and normal offline tablespaces
    # so that Oracle can avoid performing recovery on them. Oracle checks the data
    # dictionary and finds information on these absent files and marks them
    # 'MISSINGxxxx'. It then renames the missing files to acknowledge them without
    # having to recover them.
    ALTER DATABASE RENAME FILE 'MISSING0002'
         TO '/diska/prod/sales/db/fileb.dbf';